package org.iabako.server.dao.business;

import org.iabako.server.dao.JpaDAO;
import org.iabako.shared.querydefinition.columndefinition.ColumnDefinition;
import org.iabako.shared.querydefinition.dto.*;
import org.iabako.shared.tools.GenericTools;
import org.springframework.stereotype.Repository;

import javax.persistence.Query;
import java.util.*;

/**
 * TODO javadoc
 * Date: 24/11/13
 * Time: 18:22
 */
@Repository("queryDefinitionDAO")
public class QueryDefinitionDAO extends JpaDAO<Long, QueryDefinitionDTO> {

    @Override
    protected String getUniqueNumberVerificationQuery() {
        return null;
    }

    @Override
    protected String getLastNumberQuery() {
        return null;
    }

    public void executeQueryDefinition(QueryDefinitionDTO qd) {

        qd.setCustomFieldFilter(null);

        Map<Long, QueryResultDTO> queryResultDTOs = new LinkedHashMap<Long, QueryResultDTO>();

        List<CriteriaFilterDTO> allCriteria = new ArrayList<CriteriaFilterDTO>(qd.getCriterias());
        allCriteria.addAll(qd.getCriteriasOR());

        String requestSQL = qd.getRequestSQL(allCriteria);
        String requestCountSQL = qd.isCustomQuery() ? "" : qd.getRequestCountSQL(allCriteria);

        String criterias = qd.getCriterias().isEmpty() ? null : "(";
        int i = 0;
        for (CriteriaFilterDTO criteria : qd.getCriterias()) {
            criterias += formatCriteria(criteria, i++);
            if (qd.getCriterias().indexOf(criteria) != qd.getCriterias().size() - 1) {
                criterias += " AND ";
            }
        }
        criterias = !qd.getCriterias().isEmpty() ? criterias + ")" : null;

        criterias = qd.getCriteriasOR().isEmpty() ? criterias : criterias != null ? criterias + " AND (" : "(";
        for (CriteriaFilterDTO criteria : qd.getCriteriasOR()) {
            criterias += formatCriteria(criteria, i++);
            if (qd.getCriteriasOR().indexOf(criteria) != qd.getCriteriasOR().size() - 1) {
                criterias += " OR ";
            }
        }
        criterias = !qd.getCriteriasOR().isEmpty() ? criterias + ")" : criterias;

        if (!GenericTools.isEmpty(criterias) || !GenericTools.isEmpty(qd.getCustomFieldFilter())) {
            requestSQL += " WHERE ";
            requestCountSQL += " WHERE ";
        }
        if (!GenericTools.isEmpty(criterias)) {
            requestSQL += criterias;
            requestCountSQL += criterias;
        }
        if (!GenericTools.isEmpty(qd.getCustomFieldFilter())) {
            requestSQL += (GenericTools.isEmpty(criterias) ? " " : " AND ") + qd.getCustomFieldFilter();
            requestCountSQL += (GenericTools.isEmpty(criterias) ? " " : " AND ") + qd.getCustomFieldFilter();
        }

        //custom query doesn't have group by feature
        requestSQL += qd.isCustomQuery() ? "" : " " + qd.getSearchMotorQuery().getGroupByPart();

        Query countQuery = qd.isCustomQuery() ? null : createQuery(requestCountSQL, qd);
        Long countTotal = qd.isCustomQuery() ? 0L : (Long) countQuery.getSingleResult();

        if (qd.isCustomQuery() || countTotal > 0) {

            qd.setCountTotal(countTotal.intValue());

            qd.setPageNumber(new Double(Math.ceil(qd.getCountTotal() * 1.0 / qd.getPageSize())).intValue());

            if (qd.getPageNumber() < qd.getCurrentPage()) {
                qd.setCurrentPage(1);
            }

            int min = qd.getCurrentPage() != 1 ? (qd.getCurrentPage() * qd.getPageSize()) - qd.getPageSize() : 0;

            if (qd.getSortedColumn() == null) {
                qd.setSortedColumn(qd.getSearchMotorQuery().getDefaultOrderByColumn());
            }
            String sortedColumn = qd.getHqlSortedColumn();
            requestSQL += sortedColumn != null ? " ORDER BY " + sortedColumn + (qd.isAsc() ? " ASC" : " DESC") : "";

            Query query = createQuery(requestSQL, qd);
            query.setFirstResult(min);
            if (qd.getPageSize() > 0) {
                query.setMaxResults(qd.getPageSize());
            }

            List<Object[]> results = (List<Object[]>) query.getResultList();
            for (Object[] row : results) {
                QueryResultDTO qr = new QueryResultDTO();
                List<ColumnDefinition> columns;
                if (!qd.isCustomQuery()) {
                    columns = new ArrayList<ColumnDefinition>(Arrays.asList(qd.getSearchMotorQuery().getColumns()));
                    columns.removeAll(qd.getColumnsExclusion());
                } else {
                    columns = new ArrayList<ColumnDefinition>(qd.getSelectColumns());
                }

                for (ColumnDefinition columnDefinition : columns) {
                    qr.addValue(columnDefinition, null);
                }
                int j = 0;
                for (Object value : row) {
                    if (j == 0) {
                        qr.setId((Long) value);
                    } else if (j == 1) {
                        qr.setIdModificationAction((Long) value);
                    } else {
                        QueryResultValue qrValue = new QueryResultValue();
                        ColumnDefinition columnDefinition = qd.getSelectColumns().get(j - 2);
                        if (!GenericTools.isEmpty(columnDefinition.getIconClassName())) {
                            value = new IconValue(value != null ? value.toString() : null, columnDefinition.getTextEmpty(),
                                    columnDefinition.getIconClassName(), columnDefinition.getIconClassNameEmpty());
                        }
                        qrValue.setValue(value);
                        qr.addValue(columnDefinition, qrValue);
                    }
                    j++;
                }
                queryResultDTOs.put(qr.getId(), qr);
            }
            qd.setResults(queryResultDTOs);

        } else {
            qd.initialize();
            qd.setResults(queryResultDTOs);
        }
    }

    private String formatCriteria(CriteriaFilterDTO criteria, int index) {
        String stringCriteria = criteria.getKey().getHqlDefinition() + " ";
        switch (criteria.getQueryOperator()) {
            case IN:
                stringCriteria += criteria.getQueryOperator().getOperator() + " (:criteria" + index + ")";
                break;
            case ISNULL:
            case ISNOTNULL:
                stringCriteria += criteria.getQueryOperator().getOperator();
                break;
            default:
                stringCriteria += criteria.getQueryOperator().getOperator() + " :criteria" + index;
        }
        return stringCriteria;
    }

    private Query createQuery(String sqlQuery, QueryDefinitionDTO queryDefinitionDTO) {
        Query query = entityManager.createQuery(sqlQuery);
        int i = 0;
        for (CriteriaFilterDTO criteria : queryDefinitionDTO.getCriterias()) {
            if (sqlQuery.contains("criteria" + i)) {
                query.setParameter("criteria" + i, criteria.getValue());
            }
            i++;
        }
        for (CriteriaFilterDTO criteria : queryDefinitionDTO.getCriteriasOR()) {
            if (sqlQuery.contains("criteria" + i)) {
                query.setParameter("criteria" + i, criteria.getValue());
            }
            i++;
        }
        for (String parameter : queryDefinitionDTO.getCustomFieldParameters().keySet()) {
            if (sqlQuery.contains(parameter)) {
                query.setParameter(parameter, queryDefinitionDTO.getCustomFieldParameters().get(parameter));
            }
        }
        return query;
    }
}
